package Database
{
	import Database.Keys.DatabaseKey;
	
	import LessonPlans.*;
	import LessonPlans.Tasks.Task;
	
	import Utility.StringUtility;
	
	import flash.errors.SQLError;
	import flash.filesystem.File;
	
	public class DatabaseController
	{
		private const LESSON_PLAN_TABLE_NAME:String = "LessonPlans";
		private const TASK_TABLE_NAME:String = "Tasks";
		private const TEMPLATE_TABLE_NAME:String = "LessonPlanTemplates";
		private const TEMPLATE_TASK_TABLE_NAME:String = "TaskTemplates";
		
		private static var dbController:DatabaseController;
		
		public function DatabaseController(accessKey:int)
		{
			if(accessKey!=1989623)
				throw new Error("DatabaseController(): Access Denied: use init() instead");
		}
		
		static public function init():DatabaseController
		{
			if(dbController==null)
				dbController = new DatabaseController(1989623);
			return dbController;
		}
		
		public function removeLessonPlan(lessonPlanTitle:String):void
		{
			var db:DatabaseUtility=DatabaseUtility.init();
			db.removeColumns(LESSON_PLAN_TABLE_NAME,new DatabaseKey("title",lessonPlanTitle,"varchar",false));
			db.removeColumns(TASK_TABLE_NAME,new DatabaseKey("lessonPlanTitle",lessonPlanTitle,"varchar",false));
		}
		
		public function removeLessonPlanTemplate(templateTitle:String):void
		{
			var db:DatabaseUtility=DatabaseUtility.init();
			db.removeColumns(TEMPLATE_TABLE_NAME,new DatabaseKey("title",templateTitle,"varchar",false));
			db.removeColumns(TEMPLATE_TASK_TABLE_NAME,new DatabaseKey("lessonPlanTitle",templateTitle,"varchar",false));
		}
		
		/**
		 * @param lessonPlan The ILessonPlan to be inserted
		 */
		public function insertLessonPlan(lessonPlan:ILessonPlan):void
		{
			insert(lessonPlan,false);
		}
		
		/**
		 * @param template The ILessonPlan template to be inserted
		 */
		public function insertLessonPlanTemplate(template:ILessonPlan,isDefault:Boolean):void
		{
			insert(template,true);
		}
		
		private function insert(lessonPlan:ILessonPlan,isTemplate:Boolean):void
		{
			var keyList:Vector.<DatabaseKey> = new Vector.<DatabaseKey>();
			keyList.push(new DatabaseKey("title",lessonPlan.title,"varchar",true));
			keyList.push(new DatabaseKey("teacherName",lessonPlan.teacherName,"varchar",false));
			keyList.push(new DatabaseKey("date",lessonPlan.date.time,"number",false));
			
			var dbUtility:DatabaseUtility = DatabaseUtility.init();
			
			try
			{
				dbUtility.insertIntoTable(isTemplate?TEMPLATE_TABLE_NAME:LESSON_PLAN_TABLE_NAME,keyList);
			}
			catch(e:SQLError)
			{
				//check for 'not unique' error:  detailID #2206
				if(e.detailID==2206)
				{
					lessonPlan.title = StringUtility.renameDuplicateTitle(lessonPlan.title);
					insertLessonPlan(lessonPlan);
					return;
				}
				throw e;
			}
			
			for each(var task:Task in lessonPlan.taskList)
			{
				keyList = new Vector.<DatabaseKey>();
				keyList.push(new DatabaseKey("lessonPlanTitle",lessonPlan.title,"varchar",false));
				keyList.push(new DatabaseKey("taskName",task.name,"varchar",false));
				keyList.push(new DatabaseKey("description",task.description,"varchar",false));
				keyList.push(new DatabaseKey("response",task.response,"varchar",false));
				dbUtility.insertIntoTable(isTemplate?TEMPLATE_TASK_TABLE_NAME:TASK_TABLE_NAME,keyList);
			}
		}
		
		private var retrieverCallback:Function=null;
		/**
		 * @param callback Of format callback(Vector<ILessonPlan>)
		 */
		public function retrieveLessonPlans(callback:Function):void
		{
			retrieverCallback=callback;
			var dbUtility:DatabaseUtility = DatabaseUtility.init();
			dbUtility.leftOuterJoinTwoTables("LessonPlans","Tasks","LessonPlans.title","Tasks.lessonPlanTitle", triggerRetrieverCallback);
		}
		
		private function triggerRetrieverCallback(data:Array):void
		{
			var LessonPlanList:Vector.<ILessonPlan> = new Vector.<ILessonPlan>();
			//format data
			for(var i:int=0;i<data.length;i++)
			{
				if(i<1 || (data[i-1].title != data[i].title))
				{
					var d:Date = new Date();
					d.time=data[i].date;
					var lp:AbstractLessonPlan = new AbstractLessonPlan(data[i].title,data[i].teacherName,d);
					if(data[i].taskName!=null)
						lp.addTask(new Task(data[i].taskName,data[i].description,data[i].response));
					LessonPlanList.push(ILessonPlan(lp));
				}
				else
				{
					lp.addTask(new Task(data[i].taskName,data[i].description,data[i].response));
				}
			}
			
			retrieverCallback(LessonPlanList);
			retrieverCallback=null;
		}
	}
}